#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
PRESTO_HOME=/export/server/presto/bin/presto

${HIVE_HOME} -S -e "
-- 建库
-- 创建DWB层
Drop DataBase If Exists zx_dwb;
Create DataBase If Not Exists zx_dwb;

-- 切换库
Use zx_dwb;


Drop Table If Exists zx_dwb.dwb_signin_wide;
Create Table If Not Exists zx_dwb.dwb_signin_wide(
-- todo 学生打卡信息表 tbh_student_signin_record
    id                    Int      Comment '主键id',
    class_id              Int      Comment '班级id',
    student_id            Int      Comment '学员id',
    signin_time           String   Comment '签到时间',
    signin_date           String   Comment '签到日期',

-- todo 班级作息时间表 tbh_class_time_table
    morning_begin_time    String   Comment '上午开始时间',
    morning_end_time      String   Comment '上午结束时间',
    afternoon_begin_time  String   Comment '下午开始时间',
    afternoon_end_time    String   Comment '下午结束时间',
    evening_begin_time    String   Comment '晚上开始时间',
    evening_end_time      String   Comment '晚上结束时间',

-- todo 班级排课信息表 course_table_upload_detail
    class_date            String   Comment '上课日期',
    content               String   Comment '课程内容'
) comment '学生打卡信息宽表'
Row Format Delimited Fields Terminated By '\t'
Stored As ORC Tblproperties ('orc.compress' = 'SNAPPY');

Drop Table If Exists zx_dwb.dwb_leave_apply_wide;
Create Table If Not Exists zx_dwb.dwb_leave_apply_wide(
-- todo 学生请假申请表 student_leave_apply
    id                    Int      Comment '主键id',
    class_id              Int      Comment '班级id',
    student_id            Int      Comment '学员id',
    begin_time            String   Comment '请假开始时间',
    end_time              String   Comment '请假结束时间',

-- todo 班级作息时间表 tbh_class_time_table
    morning_begin_time    String   Comment '上午开始时间',
    morning_end_time      String   Comment '上午结束时间',
    afternoon_begin_time  String   Comment '下午开始时间',
    afternoon_end_time    String   Comment '下午结束时间',
    evening_begin_time    String   Comment '晚上开始时间',
    evening_end_time      String   Comment '晚上结束时间',

-- todo 班级排课信息表 course_table_upload_detail
    class_date            String   Comment '上课日期',
    content               String   Comment '课程内容'
) comment '学生请假信息宽表'
Row Format Delimited Fields Terminated By '\t'
Stored As ORC Tblproperties ('orc.compress' = 'SNAPPY');

"

wait 3

${PRESTO_HOME} --catalog hive --server hadoop01:8090 --execute "

Use hive.zx_dwb;

Insert Into hive.zx_dwb.dwb_signin_wide
Select
-- todo 学生打卡信息表 tbh_student_signin_record
    stu_sig.id,
    stu_sig.class_id,
    student_id,
    signin_time,
    signin_date,

-- todo 班级作息时间表 tbh_class_time_table
    morning_begin_time,
    morning_end_time,
    afternoon_begin_time,
    afternoon_end_time,
    evening_begin_time,
    evening_end_time,

-- todo 班级排课信息表 course_table_upload_detail
    class_date,
    content
From hive.zx_dwd.fact_tbh_student_signin_record stu_sig
Left Join hive.zx_dwd.dim_course_table_upload_detail cou_info On stu_sig.class_id = cou_info.class_id And stu_sig.signin_date = cou_info.class_date
Left Join hive.zx_dwd.dim_tbh_class_time_table class_time On stu_sig.time_table_id = class_time.id
Where cou_info.class_date Is Not Null And (stu_sig.signin_date Between class_time.use_begin_date And class_time.use_end_date);


Insert Into hive.zx_dwb.dwb_leave_apply_wide
Select
-- todo 学生请假申请表 student_leave_apply
    stu_leave.id,
    stu_leave.class_id,
    student_id,
    begin_time,
    end_time,

-- todo 班级作息时间表 tbh_class_time_table
    morning_begin_time,
    morning_end_time,
    afternoon_begin_time,
    afternoon_end_time,
    evening_begin_time,
    evening_end_time,

-- todo 班级排课信息表 course_table_upload_detail
    class_date,
    content
From hive.zx_dwd.fact_student_leave_apply stu_leave
Left Join hive.zx_dwd.dim_tbh_class_time_table class_time On stu_leave.class_id = class_time.class_id
Left Join hive.zx_dwd.dim_course_table_upload_detail cou_info On stu_leave.class_id = cou_info.class_id
Where (Substring(stu_leave.begin_time, 1, 10) Between class_time.use_begin_date And class_time.use_end_date) And (Substring(stu_leave.end_time, 1, 10) Between class_time.use_begin_date And class_time.use_end_date);

"

